"""
Case Type   : ALTER PROCEDURE
Case Name   : 修改运行时参数enable_default_ustore_table,调用存储过程时创建
              ustore表
Create At   : 2022/05/11
Owner       : @zou_jialiang050
Description :
    1.创建存储过程
    2.修改运行时参数enable_default_ustore_table为on
    3.新会话调用存储过程
    4.查询存储过程中建的表属性
    5.直接新建表2
    6.查询表2属性
    7.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.返回表属性含USTORE
    5.成功
    6.返回表2属性不含USTORE
    7.成功
History     :
"""

import os
import unittest
from testcase.utils.Constant import Constant
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger


class PROCEDURE(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.Con = Constant()
        self.procedure = 'p_alter_procedure_0047'
        self.table1 = 't_alter_procedure_0047_01'
        self.table2 = 't_alter_procedure_0047_02'

    def test_alter_procedure(self):
        step = 'step1:创建存储过程 expect:成功'
        self.logger.info(step)
        create_user = self.primary_sh.execut_db_sql(f'''
            create or replace procedure {self.procedure}()
            is
            begin
            drop table if exists {self.table1};
            create table {self.table1}(c_int int);
            end;
            ''')
        self.logger.info(create_user)
        self.assertIn(self.Con.CREATE_PROCEDURE_SUCCESS_MSG, create_user,
                      '执行失败' + step)

        step = 'step2:修改运行时参数enable_default_ustore_table为on ' \
               'expect:成功'
        self.logger.info(step)
        alter_procedure = self.primary_sh.execut_db_sql(
            f'''alter procedure p_alter_procedure_0047() set 
            enable_default_ustore_table to on;''')
        self.logger.info(alter_procedure)
        self.assertIn(self.Con.ALTER_FUNCTION_SUCCESS_MSG, alter_procedure,
                      '执行失败' + step)

        step = 'step3:新会话调用存储过程 expect:成功'
        self.logger.info(step)
        call_pro = self.primary_sh.execut_db_sql(
            f'''call {self.procedure}();''')
        self.logger.info(call_pro)
        self.assertNotIn('ERROR', call_pro, '执行失败' + step)
        self.assertIn(self.procedure, call_pro, '执行失败' + step)

        step = 'step4:查询存储过程中建的表属性 expect:返回表属性含USTORE'
        self.logger.info(step)
        select_properties = self.primary_sh.execut_db_sql(
            f'''select reloptions from pg_class where relname='{self.table1}';
            ''')
        self.logger.info(select_properties)
        self.assertIn('storage_type=ustore', select_properties,
                      '执行失败' + step)

        step = 'step5:直接新建表2 expect:成功'
        self.logger.info(step)
        create_table = self.primary_sh.execut_db_sql(
            f'''drop table if exists {self.table2};
            create table {self.table2}(c_text text);''')
        self.logger.info(create_table)
        self.assertIn(self.Con.TABLE_DROP_SUCCESS, create_table,
                      '执行失败' + step)
        self.assertIn(self.Con.TABLE_CREATE_SUCCESS, create_table,
                      '执行失败' + step)

        step = 'step6:查询表2属性 expect:返回表2属性不含USTORE'
        self.logger.info(step)
        select_properties = self.primary_sh.execut_db_sql(
            f'''select reloptions from pg_class where relname='{self.table2}';
            ''')
        self.logger.info(select_properties)
        self.assertNotIn('storage_type=USTORE', alter_procedure,
                         '执行失败' + step)

    def tearDown(self):
        step = 'step7:清理环境 expect:清理环境成功'
        self.logger.info(step)
        clean_environment = self.primary_sh.execut_db_sql(
            f'''drop table {self.table1};
            drop table {self.table2};
            drop procedure {self.procedure};''')
        self.logger.info(clean_environment)
        self.assertEqual(clean_environment.count(self.Con.TABLE_DROP_SUCCESS),
                         2, '执行失败' + step)
        self.assertIn(self.Con.DROP_PROCEDURE_SUCCESS_MSG, clean_environment,
                      '执行失败' + step)
        self.logger.info(f'-----{os.path.basename(__file__)} end-----')
